package cn.chainco.cts.idaoImpl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;

import cn.chainco.cts.idao.IVerificationDao;
import cn.chainco.cts.jdo.Company;
import cn.chainco.cts.jdo.Employee;
import cn.chainco.cts.jdo.User;
import cn.chainco.cts.jdo.Verification;
import cn.chainco.cts.utils.TimeUtil;

@SuppressWarnings("unchecked")
public class VerificationDaoImpl extends GenericHibernateDaoImpl<Verification, Long> implements IVerificationDao{

	
	/**
	 * 获取某家公司指定创建天数某种类型的结算单的总数
	 */
	public int getCountByCompany(Company company, Date date,int type){
		String sql ="select count(*) from cts_verification t where companyId=? and type=? and date_format(createTime,'%Y-%m-%d')=date_format(?,'%Y-%m-%d')";
		return ((Number)this.getSession().createSQLQuery(sql)
				.setParameter(0, company.getId())
				.setParameter(1, type)
				.setParameter(2, TimeUtil.getYYYY_MM_DD(date)).uniqueResult()).intValue();
	}
	/**
	 * 根据凭证号查询结算单
	 */
	public Verification getVerificationBycardNum(long id,Company company, String cardNum,int type){
		String hql = "from Verification where company.id="+company.getId()+" and cardNum='"+cardNum+"' and type="+type+ " and id!="+id;
		List<Verification> list = this.getSession().createQuery( hql ).list();
		if( list!=null && list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	
	/**
	 * 获取某家公司某种状态的结算单的数据
	 */
	public int getCountByCompany(Company company,int type){
		String sql ="select sum(*) from cts_verification t where companyId=? and type=?";
		return ((Number)this.getSession().createSQLQuery(sql)
				.setParameter(0, company.getId())
				.setParameter(1, type).uniqueResult()).intValue();
	}
	
	/**
	 * 获取某家公司某种状态的结算单币种分组金额 type=0为收入（收款） 1为 支出（付款） 2为奖励
	 */
	public List<Object> getMoneyByCompany(Company company,Employee employee,int type){
		String sql ="select c.type type,sum(v.money) money from cts_verification v,m68_currency c where v.currencyId=c.id and v.companyId="+company.getId() +" and v.type="+type;
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != employee.getUser().getId() ){
				sql+=" and v.id in(";
				Map<String,String> userMap = new HashMap<String,String>();
				userMap.put("employee.id", String.valueOf(employee.getId()));
				List<Object> ids = getTeamByConditions(userMap,2);
				if( ids == null || ids.size()==0 ) return new ArrayList<Object>();
				for( Object obj: ids){
					sql += String.valueOf( obj )+",";
				}
				sql +=")";
				sql = sql.replace(",)", ")");
			}
		}else if( employee.getType() == 1 ){
			sql +=" and employeeId="+employee.getId();
		}
		sql +=" group by v.currencyId";
		return this.getSession().createSQLQuery( sql ).list();
	}
	
	/**
	 * 获取某公司汇兑损益且根据币种分组
	 */
	public List<Object> getExchangeMoneyByCompany(Company company){
		String sql = "select c.type type,sum(v.exchangeMoney) exchangeMoney from cts_verification v,m68_currency c where v.currencyId=c.id and v.companyId="+company.getId() +" and isSettle=1 group by v.currencyId" ;
		return this.getSession().createSQLQuery( sql ).list();
	}
	/**
	 * 获取某公司银行手续费且根据币种分组
	 */
	public List<Object> getExchangeBlankMoneyByCompany(Company company,Employee employee){
		String sql = "select c.type type,sum(v.bankDeductionMoney) bankDeductionMoney from cts_verification v,m68_currency c where v.currencyId=c.id and v.companyId="+company.getId() ;
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != employee.getUser().getId() ){
				sql+=" and v.id in(";
				Map<String,String> userMap = new HashMap<String,String>();
				userMap.put("employee.id", String.valueOf(employee.getId()));
				List<Object> ids = getTeamByConditions(userMap,2);
				if( ids == null || ids.size()==0 ) return new ArrayList<Object>();
				for( Object obj: ids){
					sql += String.valueOf( obj )+",";
				}
				sql +=")";
				sql = sql.replace(",)", ")");
			}
		}else if( employee.getType() == 1 ){
			sql +=" and employeeId="+employee.getId();
		}
		sql +=" group by v.currencyId" ;
		return this.getSession().createSQLQuery( sql ).list();
	}
	

	/**
	 * 根据条件获取WorkTeam,type=1为or查询，type=2为and查询
	 */
	private List<Object> getTeamByConditions( Map<String,String> teamConditions,int type ){
		if( teamConditions == null || teamConditions.size()==0 )return null;
		try {
			String hql = "select w.verification.id from VerificationTeam w where";
			Set<String> key = teamConditions.keySet();
			for( Iterator<String> it = key.iterator(); it.hasNext(); ){
				String s = it.next();
				if( type == 1 ){
					hql += " or w."+s+" like'%"+teamConditions.get(s)+"%'";
				}else if( type == 2 ){
					if( s.indexOf("|")!=-1){
						hql += " and w."+s.replace("|","")+" like'%"+teamConditions.get(s)+"%'";
					}else{
						hql += " and w."+s+"='"+teamConditions.get(s)+"'";
					}
				}
			}
			hql = hql.replace("where or", "where");
			hql = hql.replace("where and", "where");
			List<Object> list = this.getSession().createQuery( hql ).list();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 获取某人在某家公司的结算单的某种类别的总数
	 */
	public int getCountByStatus( Company company,User user,Employee employee,int type){
		String sql ="select count(*) from cts_verification where companyId="+company.getId()+"  and type="+type;
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != user.getId() ){
				sql+=" and id in(";
				Map<String,String> userMap = new HashMap<String,String>();
				userMap.put("employee.user.userName", user.getUserName());
				List<Object> ids = getTeamByConditions(userMap,2);
				if( ids == null || ids.size()==0 ) return 0;
				for( Object obj: ids){
					sql += String.valueOf( obj )+",";
				}
				sql +=")";
				sql = sql.replace(",)", ")");
			}
		}else if( employee.getType() == 1 ){
			sql +=" and employeeId="+employee.getId();
		}
		
		return ((Number)this.getSession().createSQLQuery(sql).uniqueResult()).intValue();
	}
	
	/***
	 * 列表查询
	 */
	public List<Verification> getVerificationByConditon( Company company,Employee employee,User user,String keyword,int dateType,int type,int pageSize,int pageNumber ){
		int row = (pageNumber - 1) * pageSize;
		String hql = "from Verification v where v.company.id="+company.getId();
		if( keyword!=null && keyword.trim().length()>0 ){
			hql +=" and( v.cardNum like'%"+keyword+"%' or v.employee.nickname like '%"+keyword+"%')";
		}
		if( type!=999 ){
			hql +=" and v.type="+type;
		}
		if( dateType != 999 && dateType != 1 ){//近xx天
			hql +=" and v.createTime>=? and v.createTime<=?";
		}else if( dateType == 1 ){//当天
			hql +=" and v.createTime=?";
		}
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != user.getId() ){
				hql +=" and v.id in(:user)";
			}
		}else if(  employee.getType() == 1){
			hql +=" and v.employee.id="+employee.getId();
		}
		
		hql +=" order by v.createTime desc";
		Query query = this.getSession().createQuery(hql);
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != user.getId() ){
				Map<String,String> userMap = new HashMap<String,String>();
				userMap.put("employee.user.userName", user.getUserName());
				List<Object> ids = getTeamByConditions(userMap,2);
				if( ids == null || ids.size()==0 ) return new ArrayList<Verification>();
				query.setParameterList("user",ids);
			}
		}
		SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		try {
			date = simpledateformat.parse( simpledateformat.format( date ));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		if( dateType != 999 && dateType != 1 ){//近xx天
			query.setDate(0,  TimeUtil.beforeCurrentDate( dateType ));
			query.setDate(1,  date);
		}else if( dateType == 1 ){//当天
			query.setDate(0,  date);
		}
		List<Verification>listVerification = query.setFirstResult(row).setMaxResults(pageSize).list();
		return listVerification;
	}

	/**
	 * 获取列表查询的总数
	 */
	public int getCountVerificationByConditon( Company company,Employee employee,User user,String keyword,int dateType,int type ){
		String sql ="select count(*) from cts_verification where companyId="+company.getId();
		if( keyword!=null && keyword.trim().length()>0 ){
			sql +=" and ( cardNum like '%"+keyword+"%'";
			Map<String,String> userMap = new HashMap<String,String>();
			userMap.put("employee.nickname", keyword);
			List<Object> ids = getTeamByConditions(userMap,2);
			if( ids != null && ids.size()!=0 ){
				sql += " or id in(:employee)) ";
			}else{
				sql += ")";
			}
			 
		}
		if( type!=999 ){
			sql +=" and type="+type;
		}
		if( dateType != 999 && dateType != 1 ){//近xx天
			sql +=" and createTime>=? and createTime<=?";
		}else if( dateType == 1 ){//当天
			sql +=" and createTime=?";
		}
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != user.getId() ){
				sql +=" and id in(:user)";
			}
		}else if(  employee.getType() == 1){
			sql +=" and employeeId="+employee.getId();
		}
		
		
		Query query = this.getSession().createSQLQuery(sql);
		
		if( employee.getType() == 0 ){
			if( company.getUser().getId() != user.getId() ){
				Map<String,String> userMap = new HashMap<String,String>();
				userMap.put("employee.user.userName", user.getUserName());
				List<Object> ids = getTeamByConditions(userMap,2);
				if( ids == null || ids.size()==0 ) return 0;
				query.setParameterList("user",ids);
			}
		}
		if( keyword!=null && keyword.trim().length()>0 ){
			Map<String,String> userMap = new HashMap<String,String>();
			userMap.put("employee.nickname", keyword);
			List<Object> ids = getTeamByConditions(userMap,2);
			if( ids != null && ids.size()!=0 ){
				query.setParameterList("employee",ids);
			}
		}
		
		SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		try {
			date = simpledateformat.parse( simpledateformat.format( date ));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		if( dateType != 999 && dateType != 1 ){//近xx天
			query.setDate(0,  TimeUtil.beforeCurrentDate( dateType ));
			query.setDate(1,  date);
		}else if( dateType == 1 ){//当天
			query.setDate(0,  date);
		}
		int number = ((Number)query.uniqueResult()).intValue();
		return number;
	}

}